import sqlite3
from typing import List, Tuple, Any, Dict

from logger_utils import logger


class SQLiteTool:
    def __init__(self):
        """
        初始化 SQLite 工具类。

        参数:
        - db_path: 数据库文件路径。
        """
        self.db_path = "./Data/DDK_DK_45K1/db/cms.db"
        # self.db_path = "./cms.db"

    @staticmethod
    def construct_where_clause(data: Dict[str, Any]) -> Tuple[str, Tuple]:
        where_conditions = " AND ".join(f"{k} = ?" for k in data.keys())
        where_values = tuple(data.values())
        return where_conditions, where_values

    def dynamic_insert_or_update(self, table_name: str, data: Dict[str, Any]) -> int:
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()

            if table_name == 'T_BASIC_INFO':
                task_id = data.get("task_id")
                t_id = data.get("t_id")

                if task_id is None or t_id is None:
                    raise ValueError("Both task_id and t_id should be present in the data for T_BASIC_INFO.")

                cursor.execute("SELECT 1 FROM T_BASIC_INFO WHERE task_id=? AND t_id=?", (task_id, t_id))
                exists = cursor.fetchone()

                if exists:
                    columns = ', '.join(f"{k} = ?" for k in data.keys())
                    sql = f"UPDATE {table_name} SET {columns} WHERE task_id=? AND t_id=?"
                    params = tuple(data.values()) + (task_id, t_id)
                else:
                    columns = ', '.join(data.keys())
                    placeholders = ', '.join('?' for _ in data)
                    sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
                    params = tuple(data.values())

                cursor.execute(sql, params)
                conn.commit()

                # Ensure the correct ID is returned
                if exists:
                    where_clause, where_params = self.construct_where_clause(data)
                    cursor.execute(f"SELECT id FROM {table_name} WHERE {where_clause}", where_params)
                    return cursor.fetchone()[0]
                else:
                    return cursor.lastrowid

            elif table_name == 'T_RATIO_PARAM':
                info_id = data.get("info_id")

                if info_id is None:
                    raise ValueError("info_id should be present in the data for T_RATIO_PARAM.")

                cursor.execute("SELECT 1 FROM T_RATIO_PARAM WHERE info_id=?", (info_id,))
                exists = cursor.fetchone()

                if exists:
                    columns = ', '.join(f"{k} = ?" for k in data.keys())
                    sql = f"UPDATE {table_name} SET {columns} WHERE info_id=?"
                    params = tuple(data.values()) + (info_id,)
                else:
                    columns = ', '.join(data.keys())
                    placeholders = ', '.join('?' for _ in data)
                    sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
                    params = tuple(data.values())

                cursor.execute(sql, params)
                conn.commit()

                # Ensure the correct ID is returned
                if exists:
                    where_clause, where_params = self.construct_where_clause(data)
                    cursor.execute(f"SELECT id FROM {table_name} WHERE {where_clause}", where_params)
                    return cursor.fetchone()[0]
                else:
                    return cursor.lastrowid
            else:
                raise ValueError(f"Unsupported table_name: {table_name}")

    def query(self, sql: str, params: Tuple[Any] = ()) -> List[Tuple[Any]]:
        """
        查询操作。

        参数:
        - sql: 查询的SQL语句。
        - params: SQL语句中的参数。

        返回:
        - 查询结果的列表。
        """
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute(sql, params)
            rows = cursor.fetchall()
            return rows

    def insert(self, sql: str, params: Tuple[Any] = ()) -> int:
        """
        插入操作。

        参数:
        - sql: 插入的SQL语句。
        - params: SQL语句中的参数。

        返回:
        - 插入数据后的 row ID。
        """
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute(sql, params)
            conn.commit()
            return cursor.lastrowid


if __name__ == "__main__":

    # 插入示例
    # insert_sql = "INSERT INTO users (name, age) VALUES (?, ?)"
    # new_id = db_tool.insert(insert_sql, ("Alice", 30))
    # print(f"Inserted new user with ID: {new_id}")

    # 查询
    db_tool = SQLiteTool()
    query_table_sql = "SELECT name FROM sqlite_master WHERE type='table'"
    query_sql = "SELECT * FROM T_ERROR_DET"

    results = db_tool.query(query_table_sql)
    logger.debug(results)
    ii = 0
    for row in results:
        # logger.debug(row)
        query_sql = f"SELECT * FROM {row[0]}"
        res = db_tool.query(query_sql)
        for item in res:
            ii += 1
            logger.debug(f'{row[0]}: {item}')
        pass
    logger.debug(ii)
